<?php

namespace app\modules\cruise\models;

use Yii;
use app\modules\cruise\models\Cruise;
use app\modules\cruise\models\RoomUse;
use app\modules\cruise\models\RoomDownload;

use Overtrue\Pinyin\Pinyin;


class Excel2 extends \yii\db\ActiveRecord
{
    
    
    //模板 3 歌诗达&地中海
    public function Template3($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }
        if(empty($room_arr)){
            return false;
        }

        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(11)->setName('宋体');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(16);

        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:Z1')->applyFromArray($styleArray);

        $sheet->getColumnDimension('A')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(12);// 设置宽度
        $sheet->getRowDimension(1)->setRowHeight(30);    

        $titleArray = ['CRUISE ID','舱型','舱号','最大入住人数','团号','姓','名','性别','生日','国籍','护照号码','签发日期','有效日期','NATIONALITY ID','LANGUUAGE','出生地','签发地','HOME CITY','HOME STATE','HOME COUNTRY','DINING choice （用餐批次，M/L）','中文名','旅行社紧急联系人','紧急联系方式','床型','分餐'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        
        $sheet->getStyle("A1:Z1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:Z1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('A5A5A5');
        $sheet->getStyle('A1:Z1')->getFont()->setSize(12)->setBold(true)->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);

        $rooms_model = RoomUse::find()->where(['in','id',$room_arr])->all(); 
        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_3 = yii::$app->params['area']['country_code_3'];
        $key = 0;

        //先找房间 再找人
        foreach($rooms_model as $room){       
            //房间名称 
            $room_name = $room->room->name.$room->room->code.' '.$room->num.'人间';
            //房间号
            $room_code = $room->code;
            //最大入住人数
            $room_num = $room->room->num;
            foreach($room->allCustomer as $val){   
                $birthday = !empty($val->birthday) ? date("d/m/Y",strtotime($val->birthday)) :'';
                $sheet->setCellValue(sprintf("A%d",$key+2),"");
                $sheet->setCellValue(sprintf("B%d",$key+2),$room_name);
                $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room_code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("D%d",$key+2),$room_num);
                $sheet->setCellValue(sprintf("E%d",$key+2),$val->group);
                $sheet->setCellValue(sprintf("F%d",$key+2),$val->en_first_name);
                $sheet->setCellValue(sprintf("G%d",$key+2),$val->en_last_name);
                $sheet->setCellValue(sprintf("H%d",$key+2),yii::$app->params['gender_en'][$val->gender]);
                $sheet->setCellValue(sprintf("I%d",$key+2),$birthday);
                $sheet->setCellValue(sprintf("J%d",$key+2),!empty($country_code_3[$val->country])?$country_code_3[$val->country]:$val->country);
                $sheet->setCellValue(sprintf("K%d",$key+2),$val->passport);
                $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->passport_grant) ? date("d/m/Y",strtotime($val->passport_grant)) :'');
                $sheet->setCellValue(sprintf("M%d",$key+2),!empty($val->passport_date) ? date("d/m/Y",strtotime($val->passport_date)) :'');
                $sheet->setCellValue(sprintf("N%d",$key+2),"");
                $sheet->setCellValue(sprintf("O%d",$key+2),"");
                $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->native]))?strtoupper($province[$val->native]) : $val->native);
                $sheet->setCellValue(sprintf("Q%d",$key+2),(!empty($province[$val->passport_add]))?strtoupper($province[$val->passport_add]) : $val->passport_add);
                $sheet->setCellValue(sprintf("R%d",$key+2),"");
                $sheet->setCellValue(sprintf("S%d",$key+2),"");
                $sheet->setCellValue(sprintf("T%d",$key+2),"");
                $sheet->setCellValue(sprintf("U%d",$key+2),"");
                $sheet->setCellValue(sprintf("V%d",$key+2),$val->first_name.$val->last_name);
                $sheet->setCellValue(sprintf("W%d",$key+2),"");
                $sheet->setCellValueExplicit(sprintf("X%d",$key+2),$val->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("Y%d",$key+2),"双床");
                $sheet->setCellValue(sprintf("Z%d",$key+2),"");
                $key++;
            }       
      
        }    
       

        $sheet->getStyle(sprintf('A1:Z%d',$key+1))->applyFromArray($styleArray);
      
        $sheet->setAutoFilter("A1:Z1");

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();

        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template'][3],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 3;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;

    }

    //模板 4 皇家海外名单
    public function Template4($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }
        if(empty($room_arr)){
            return false;
        }
        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial Unicode MS');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(20);
        $sheet->getColumnDimension('A')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(12);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(12);// 设置宽度

        $sheet->getStyle('A:C')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('F:G')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('J:K')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('R:S')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('V:W')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AD:AE')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AH:AI')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AP:AQ')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('AT:AU')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getColumnDimension('F')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('K')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('R')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('V')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('W')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AD')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AE')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AH')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AI')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AP')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AQ')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AT')->setWidth(20);// 设置宽度
        $sheet->getColumnDimension('AU')->setWidth(20);// 设置宽度
        $titleArray = ['订单号','团队号','舱型','等级','船舱号码','第一个人的姓','第一个客人的名','第一个客人的用餐批次','这个部分只放C/O','第一个客人的国籍','第一个客人的生日','第一个客人的年龄（不用填写） ',' 年龄范围（不用填写）','第一个客人的性别','第一个客人的护照号','护照有效期','紧急联系人号码',' 第二个人的姓','第二个客人的名','第二个客人的用餐批次','这个部分只放C/O','第二个客人的国籍',' 第二个客人的生日','第二个客人的年龄（不用填写）','  年龄范围（不用填写）','第二个人客人的性别 ','第二个客人的护照号 ','护照有效期 ','紧急联系人号码',' 第三个人的姓  ','第三个客人的名 ','第三个客人的用餐批次 ',' 这个部分只放C/O ','第三个客人的国籍  ','第三个客人的生日','第三个客人的年龄（不用填写）','  年龄范围（不用填写）','  第三个人客人的性别',' 第三个客人的护照号',' 护照有效期 ','紧急联系人号码 ','第四个人的姓','  第四个客人的名 ','第四个客人的用餐批次 ',' 这个部分只放C/O ','第四个客人的国籍 ',' 第四个客人的生日 ',' 第四个客人的年龄（不用填写） ',' 年龄范围（不用填写）','  第四个人客人的性别 ','第四个客人的护照号',' 护照有效期 ','紧急联系人号码'];

        $sheet->fromArray($titleArray, NULL, 'A1');

        $titleArray2 = ['ORDER_ID','GROUP_ID','OCCUPANCY',' CATEGORY ',' CABIN ','GUEST_1_LAST_NAME ','GUEST_1_FIRST_NAME','  GUEST_1_DINING ',' GUEST_1_AIR ','GUEST_1_CTZ',' GUEST_1_DOB ','GUEST_1_AGE ','GUEST_1_AGE_RANGE',' GUEST_1_GENDER','  GUEST_1_PASSPORTNUMBER','  GUEST_1_EXPIRYDATE ',' GUEST_1_EMERGENCYCONTACTNUMBER ',' GUEST_2_LAST_NAME ','GUEST_2_FIRST_NAME ',' GUEST_2_DINING ',' GUEST_2_AIR ','GUEST_2_CTZ',' GUEST_2_DOB ','GUEST_2_AGE',' GUEST_2_AGE_RANGE',' GUEST_2_GENDER','  GUEST_2_PASSPORTNUMBER ',' GUEST_2_EXPIRYDATE ',' GUEST_2_EMERGENCYCONTACTNUMBER','  GUEST_3_LAST_NAME',' GUEST_3_FIRST_NAME ',' GUEST_3_DINING','  GUEST_3_AIR ','GUEST_3_CTZ ','GUEST_3_DOB ','GUEST_3_AGE',' GUEST_3_AGE_RANGE ','GUEST_3_GENDER ',' GUEST_3_PASSPORTNUMBER','  GUEST_3_EXPIRYDATE ',' GUEST_3_EMERGENCYCONTACTNUMBER ',' GUEST_4_LAST_NAME',' GUEST_4_FIRST_NAME ',' GUEST_4_DINING ',' GUEST_4_AIR',' GUEST_4_CTZ',' GUEST_4_DOB',' GUEST_4_AGE ','GUEST_4_AGE_RANGE',' GUEST_4_GENDER ',' GUEST_4_PASSPORTNUMBER ',' GUEST_4_EXPIRYDATE','  GUEST_4_EMERGENCYCONTACTNUMBER'];
        $sheet->fromArray($titleArray2, NULL, 'A2');

        $rooms_model = RoomUse::find()->where(['in','id',$room_arr])->all(); 

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province); 
        $province = array_merge($province,yii::$app->params['area']['country_english']); 
        $country_code_3 = yii::$app->params['area']['country_code_3'];

        $key = 0;

        foreach($rooms_model as $room){  
            
            $sheet->setCellValueExplicit(sprintf("A%d",$key+3),'',\PHPExcel_Cell_DataType::TYPE_STRING);
          
            $sheet->setCellValue(sprintf("B%d",$key+3),''); 
          
            $room_name = $room->room->name.$room->room->code;
            $room_code = $room->code;
            $room_num = $room->num.'人';

            $sheet->setCellValue(sprintf("C%d",$key+3),$room_name);
            $sheet->setCellValue(sprintf("D%d",$key+3),$room_num);
            $sheet->setCellValue(sprintf("E%d",$key+3),$room_code);

            $customer_msg_arr = [];
            $customers = $room->allCustomer;
            if(!empty($customers)){
                foreach($customers as $cust){
                    if($cust->status == 1){
                      $customer_msg_arr[] = $cust->en_first_name;
                      $customer_msg_arr[] = $cust->en_last_name;
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = "C/O";
                      $customer_msg_arr[] = !empty($country_code_3[$cust->country])?$country_code_3[$cust->country]:$cust->country;
                      $customer_msg_arr[] = !empty($cust->birthday) ? date("d/m/Y",strtotime($cust->birthday)) :'';
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->gender];
                      $customer_msg_arr[] = $cust->passport;
                      $customer_msg_arr[] = !empty($cust->passport_date) ? date("d/m/Y",strtotime($cust->passport_date)) :'';
                      $customer_msg_arr[] = sprintf("%s",$cust->phone);
                    }  
                }  
            }
         
            $sheet->fromArray($customer_msg_arr, NULL, sprintf("F%d",$key+3));
            $key++;
            
        }

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();

        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template'][4],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 4;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 5 皇家母港名单
    public function Template5($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }
        if(empty($room_arr)){
            return false;
        }

        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setBold(true)->setName('Trebuchet MS');
        $sheet->getDefaultStyle()->getFont()->getColor()->setARGB('333333');
        $sheet->getDefaultColumnDimension()->setWidth(16);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:CF2')->applyFromArray($styleArray);
        $sheet->getColumnDimension('A')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(16);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(30);
        $sheet->getRowDimension(2)->setRowHeight(20);   
        $sheet->getStyle('D')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('G:H')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('K:M')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('S')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF'); 
        $sheet->getStyle('U:X')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AA:AB')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AE:AG')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');  
        $sheet->getStyle('AM')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');    
        $sheet->getStyle('AO:AR')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');      
        $sheet->getStyle('AU:AV')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('AY:BA')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BG')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BI:BL')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BO:BP')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('BS:BU')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('CA')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');
        $sheet->getStyle('CC:CE')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('BFBFBF');

        $titleArray = ['订单号','团队号','入住人数 ','船舱类型 ',' 船舱号码  ','第一个客人称谓的英文  ','第一个客人姓的拼音 ','第一个客人名的拼音 ','第一个客人中间名的拼音 ','第一个客人的中文称谓','  第一个客人姓的中文',' 第一个客人名的中文',' 第一个客人中间名的中文',' 第一个客人所在省份的中文 ',' 第一个客人所在省份的拼音','  第一个客人的用餐批次','  第一个客人的船票类型(只放C/O)',' 第一个客人的国籍','  第一个客人的生日','  第一个客人的性别','  研讨会编号',' 领队 ',' 第一个客人的护照号',' 第一个客人的护照有效期 ','紧急联系人号码 ','第二个客人称谓的英文 ',' 第二个客人姓的拼音',' 第二个客人名的拼音',' 第二个客人中间名的拼音',' 第二个客人的中文称谓 ',' 第二个客人姓的中文',' 第二个客人名的中文',' 第二个客人中间名的中文',' 第二个客人所在省份的中文 ',' 第二个客人所在省份的拼音','  第二个客人的用餐批次 ',' 第二个客人的船票类型(只放C/O) ','第二个客人的国籍 ',' 第二个客人的生日  ','第二个人客人的性别',' 研讨会编号',' 领队','  第二个客人的护照号',' 护照有效期 ','紧急联系人号码 ','第三个客人称谓的英文 ',' 第三个客人姓的拼音',' 第三个客人名的拼音 ','第三个客人中间名的拼音',' 第三个客人的中文称谓 ',' 第三个客人姓的中文',' 第三个客人名的中文',' 第三个客人中间名的中文 ','第三个客人所在省份的中文 ',' 第三个客人所在省份的拼音','  第三个客人的用餐批次 ',' 第三个客人的船票类型(只放C/O) ','第三个客人的国籍','  第三个客人的生日 ',' 第三个人客人的性别',' 研讨会编号',' 领队 ',' 第三个客人的护照号 ','护照有效期','紧急联系人号码 ','第四个客人称谓的英文',' 第四个客人姓的拼音',' 第四个客人名的拼音 ','第四个客人中间名的拼音',' 第四个客人的中文称谓 ',' 第四个客人姓的中文',' 第四个客人名的中文',' 第四个客人中间名的中文',' 第四个客人所在省份的中文','  第四个客人所在省份的拼音','  第四个客人的用餐批次  ','第四个客人的船票类型(只放C/O) ','第四个客人的国籍 ',' 第四个客人的生日','  第四个人客人的性别',' 研讨会编号',' 领队 ',' 第四个客人的护照号',' 护照有效期 ','紧急联系人号码',' 1'];
        $sheet->fromArray($titleArray, NULL, 'A1');
        $sheet->getStyle('A1:CF1')->getFont()->setSize(8)->setName('宋体');

        $titleArray1 = ['ORDER_ID','GROUP_ID','OCCUPANCY ',' CATEGORY  ',' CABIN ','  TITLE1 ',' PAX1_LAST_NAME ',' PAX1_FIRST_NAME',' PAX1_MIDDLE_NAME  ','  TITLE1_CHN ',' PAX1_LAST_NAME_CHN','  PAX1_FIRST_NAME_CHN ','PAX1_MIDDLE_NAME_CHN ','   PAX1_PROVINCE_CHN ','  PAX1_PROVINCE','   PAX1_DINING',' PAX1_AIR ','   PAX1_CTZ  ','  PAX1_DOB  ','  PAX1_GENDER ','PAX1_CHARTER # ',' PAX1_LEADER',' PAX1_PASSPORT # ','PAX1_EXPIRY DATE',' PAX1_EMERGENCYPHONE','  TITLE2 ',' PAX2_LAST_NAME  ','PAX2_FIRST_NAME ','PAX2_MIDDLE_NAME  ','  TITLE2_CHN  ','PAX2_LAST_NAME_CHN ',' PAX2_FIRST_NAME_CHN ','PAX2_MIDDLE_NAME_CHN ','   PAX2_PROVINCE_CHN  ',' PAX2_PROVINCE ','  PAX2_DINING',' PAX2_AIR ','   PAX2_CTZ  ','  PAX2_DOB ','   PAX2_GENDER ','PAX2_CHARTER #  ','PAX2_LEADER ','PAX2_PASSPORT # ','PAX2_EXPIRYDATE ','   PAX2_EMERGENCYPHONE  ','  TITLE3 ',' PAX3_LAST_NAME ',' PAX3_FIRST_NAME',' PAX3_MIDDLE_NAME ','   TITLE3_CHN ',' PAX3_LAST_NAME_CHN ',' PAX3_FIRST_NAME_CHN ','PAX3_MIDDLE_NAME_CHN  ','  PAX3_PROVINCE_CHN ','  PAX3_PROVINCE','   PAX3_DINING ','PAX3_AIR  ','  PAX3_CTZ ','   PAX3_DOB ','   PAX3_GENDER',' PAX3_CHARTER # ',' PAX3_LEADER ','PAX3_PASSPORT # ','PAX3_EXPIRYDATE ','   PAX3_EMERGENCYPHONE ','   TITLE4  ','PAX4_LAST_NAME ',' PAX4_FIRST_NAME',' PAX4_MIDDLE_NAME ','   TITLE4_CHN','  PAX4_LAST_NAME_CHN','  PAX4_FIRST_NAME_CHN ','PAX4_MIDDLE_NAME_CHN ','   PAX4_PROVINCE_CHN  ',' PAX4_PROVINCE','   PAX4_DINING ','PAX4_AIR  ','  PAX4_CTZ','    PAX4_DOB ','   PAX4_GENDER ','PAX4_CHARTER #  ','PAX4_LEADER ','PAX4_PASSPORT # ','PAX4_EXPIRYDATE ','   PAX4_EMERGENCYPHONE  ','  Edited'];
        $sheet->fromArray($titleArray1, NULL, 'A2');
        $sheet->getStyle('A2:CF2')->getFont()->setSize(10)->setName('Arial');
        $sheet->getStyle('A2:CF2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');
     
        $sheet->freezePane('D3');
        $sheet->setAutoFilter('A2:CF2');

        $rooms_model = RoomUse::find()->where(['in','id',$room_arr])->all(); 

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province); 
        $province = array_merge($province,yii::$app->params['area']['country_english']);     
        $country_code_3 = yii::$app->params['area']['country_code_3'];

        $key = 0;

        foreach($rooms_model as $room){ 
          
              $sheet->setCellValueExplicit(sprintf("A%d",$key+3),'',\PHPExcel_Cell_DataType::TYPE_STRING);
              
              $sheet->setCellValue(sprintf("B%d",$key+3),'');

              $room_name = $room->room->name;
              $room_code = $room->room->code;
              $room_num = $room->num.'人';

              $sheet->setCellValue(sprintf("C%d",$key+3),$room_num);
              $sheet->setCellValue(sprintf("D%d",$key+3),$room_name.$room_code);
            
              $sheet->setCellValueExplicit(sprintf("E%d",$key+3),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);

              $customer_msg_arr = [];
            
              $customers = $room->allCustomer;
              if(!empty($customers)){
                  foreach($customers as $cust){
                    if($cust->status == 1){
                      $customer_msg_arr[] = "";  
                      $customer_msg_arr[] = $cust->en_first_name;
                      $customer_msg_arr[] = $cust->en_last_name;
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = $cust->first_name;
                      $customer_msg_arr[] = $cust->last_name;
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = $cust->native;
                      $customer_msg_arr[] = (!empty($province[$cust->native]))?strtoupper($province[$cust->native]) : $cust->native;
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = "C/O";
                      $customer_msg_arr[] = !empty($country_code_3[$cust->country])?$country_code_3[$cust->country]:$cust->country;
                      $customer_msg_arr[] = !empty($cust->birthday) ? date("d/m/Y",strtotime($cust->birthday)) :'';
                      $customer_msg_arr[] = yii::$app->params['gender_en'][$cust->gender];
                      $customer_msg_arr[] = "";
                      $customer_msg_arr[] = "N";
                      $customer_msg_arr[] = $cust->passport;
                      $customer_msg_arr[] = !empty($cust->passport_date) ? date("d/m/Y",strtotime($cust->passport_date)) :'';
                      $customer_msg_arr[] = $cust->phone;
                    }  
                  }  
              }
             
              $sheet->fromArray($customer_msg_arr, NULL, sprintf("F%d",$key+3));
              $key++;
             
        }
       

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);
        
        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();

        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template'][5],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 5;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;

    }

    //模板 6 NCL名单
    public function Template6($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }

        if(empty($room_arr)){
            return false;
        }

        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
        $sheet->getDefaultColumnDimension()->setWidth(16);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:P1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:P1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(16);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(16);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(50);       

        $titleArray = ['航程日期 Sailing Date (DD/MM/YYYY)','团队号 Group ID','订单号 Order ID','房型 CATEGORY','房间号 CABIN','姓(拼音) LAST NAME','名 (拼音) FIRST NAME','姓 (中文) LAST NAME (Mandarin)','名 (中文) FIRST NAME(Mandarin)','性别SEX(F/M)','国籍 NATIONALITY(use Country Codes Table)','出生日期(日/月/年) BIRTH DATE (DD/MM/YYYY)','护照号码 PASSPORT NUMBER','签发日期 (日/月/年) ISSUE DATE (DD/MM/YYYY)','有效期 (日/月/年) EXPIRE DATE(DD/MM/YYYY)','护照签发地PASSPORT_PLACE_ISSUED'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:P1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('A5A5A5');
        $sheet->getStyle('A1:P1')->getFont()->setSize(8)->setBold(true)->getColor()->setARGB('000000');

        $rooms_model = RoomUse::find()->where(['in','id',$room_arr])->all();          

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 0;
    
        //先找房间 再找人
        foreach($rooms_model as $room){
            //房间名称
            $room_name = $room->room->name.$room->room->code." ".$room->num.'人间';
            //房间号
            $room_code = $room->code;
            //最大入住人数
            $room_num = $room->room->num;

            foreach($room->allCustomer as $val){   
                $sheet->setCellValue(sprintf("A%d",$key+2),"");
                $sheet->setCellValue(sprintf("B%d",$key+2),$val->group);
                $sheet->setCellValueExplicit(sprintf("C%d",$key+2),'',\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("D%d",$key+2),$room_name);
                $sheet->setCellValue(sprintf("E%d",$key+2),$room->code);
                $sheet->setCellValue(sprintf("F%d",$key+2),$val->en_first_name);
                $sheet->setCellValue(sprintf("G%d",$key+2),$val->en_last_name);
                $sheet->setCellValue(sprintf("H%d",$key+2),$val->first_name);
                $sheet->setCellValue(sprintf("I%d",$key+2),$val->last_name);
                $sheet->setCellValue(sprintf("J%d",$key+2),yii::$app->params['gender_en'][$val->gender]);
                $sheet->setCellValue(sprintf("K%d",$key+2),(!empty($country_code_2[$val->country]))?$country_code_2[$val->country]:$val->country);
                $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->birthday) ? date("d/m/Y",strtotime($val->birthday)) :'');
                $sheet->setCellValue(sprintf("M%d",$key+2),$val->passport);
                $sheet->setCellValue(sprintf("N%d",$key+2),!empty($val->passport_grant) ? date("d/m/Y",strtotime($val->passport_grant)) :'');
                $sheet->setCellValue(sprintf("O%d",$key+2),!empty($val->passport_date) ? date("d/m/Y",strtotime($val->passport_date)) :'');
                $sheet->setCellValue(sprintf("P%d",$key+2),(!empty($province[$val->passport_add]))?strtoupper($province[$val->passport_add]) : "");
                $key++;
            }       
        }   

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();
        
        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template'][6],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 6;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }

    //模板 8 处女星号名单
    public function Template8($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }
        if(empty($room_arr)){
            return false;
        }

        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('微软雅黑');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $styleArray = [
             'borders' => [
                 'allborders' => ['style'=>\PHPExcel_Style_Border::BORDER_THIN,'color'=>['argb'=>'000000']],
             ],
        ]; //设置border
        $sheet->getStyle('A1:S1')->applyFromArray($styleArray);
        $sheet->getStyle('A1:S1')->getAlignment()->setWrapText(true);
        $sheet->getColumnDimension('A')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('B')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('C')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('D')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('E')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('F')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('G')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('H')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('I')->setWidth(10);// 设置宽度
        $sheet->getColumnDimension('J')->setWidth(10);// 设置宽度 
        $sheet->getColumnDimension('K')->setWidth(10);// 设置宽度 
        $sheet->getColumnDimension('L')->setWidth(12);// 设置宽度 
        $sheet->getColumnDimension('O')->setWidth(20);// 设置宽度 
        $sheet->getRowDimension(1)->setRowHeight(30);       

                                     
        $titleArray = ['团号','房间类型','房间序号','客人姓名','姓（拼音）','名（拼音）','性别','出生日期','出生地','护照号','护照签发地','护照签发日期','护照有效期','手机号','身份证号','国籍','备注','户籍住址','职业现状（所在公司或单位名称需要填写清楚）'];
        $sheet->fromArray($titleArray, NULL, 'A1');

        $sheet->getStyle("A1:S1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('D8D8D8');
        $sheet->getStyle('A1:P1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('000000');

        $rooms_model = RoomUse::find()->where(['in','id',$room_arr])->all();

        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 0;

        //先找房间 再找人
        foreach($rooms_model as $room){
          
            //房间名称
            $room_name = $room->room->name.$room->room->code." ".$room->num.'人间';
            //房间类型
            $room_type = $room->room->code;
            //房间号
            $room_code = $room->code;
            //最大入住人数
            $room_num = $room->room->num;
            foreach($room->allCustomer as $val){   
                $sheet->setCellValue(sprintf("A%d",$key+2),$val->group);
                $sheet->setCellValue(sprintf("B%d",$key+2),$room_type);
                $sheet->setCellValueExplicit(sprintf("C%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("D%d",$key+2),$val->first_name.$val->last_name);
                $sheet->setCellValue(sprintf("E%d",$key+2),$val->en_first_name);
                $sheet->setCellValue(sprintf("F%d",$key+2),$val->en_last_name);
                $sheet->setCellValue(sprintf("G%d",$key+2),yii::$app->params['gender'][$val->gender]);
                $sheet->setCellValue(sprintf("H%d",$key+2),!empty($val->birthday) ? date("Y-m-d",strtotime($val->birthday)) :'');
                $sheet->setCellValue(sprintf("I%d",$key+2),$val->native);
                $sheet->setCellValue(sprintf("J%d",$key+2),$val->passport);
                $sheet->setCellValue(sprintf("K%d",$key+2),$val->passport_add);
                $sheet->setCellValue(sprintf("L%d",$key+2),!empty($val->passport_grant) ? date("Y-m-d",strtotime($val->passport_grant)) :'');
                $sheet->setCellValue(sprintf("M%d",$key+2), !empty($val->passport_date) ? date("Y-m-d",strtotime($val->passport_date)) :'');
                $sheet->setCellValueExplicit(sprintf("N%d",$key+2),$val->phone,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$val->card,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("P%d",$key+2), (!empty($country_code_2[$val->country]))?$country_code_2[$val->country]:$val->country);
                $sheet->setCellValue(sprintf("R%d",$key+2),"");
                $sheet->setCellValue(sprintf("S%d",$key+2),"");
                $key++;
            }       

        }   

       

        $sheet->getStyle(sprintf("A2:S%d",$key+1))->applyFromArray($styleArray);

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();
        
        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template_2'][8],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 8;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }


    //模板 9 世界梦号名单
    public function Template9($cruise_id,$room_arr)
    {
        if(empty($cruise_id)){
            return false;
        }
        if(empty($room_arr)){
            return false;
        }

        $cruise = Cruise::findOne($cruise_id);

        $objPHPExcel = new \PHPExcel();
        $sheet = $objPHPExcel->getActiveSheet(0);
        $sheet->setTitle('团队名单');
        $sheet->getDefaultStyle()->getFont()->setSize(10)->setName('Arial');
        $sheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $sheet->getDefaultColumnDimension()->setWidth(10);
        $sheet->getRowDimension(1)->setRowHeight(40); 
        $sheet->getRowDimension(2)->setRowHeight(40);

        $titleArray1 = ['Booking Source','Initial Date','Agent ID','Currency Code','Market Code','Office Code','Agr ID','Agent Reference','Agent Contact','Advert Code','Pax Type','Cost Center','Cruise ID','Category Code','Cabin Number','Price ID/ Promo Code','Greeting','Gender','Firstname','Lastname','Localized First Name', 'Localized Last Name','Date of Birth','Nationality','Residency','Client Language','Passport No.','Passport Issued At','Passport Issue Date','Passport Expiry Date','National ID','National ID Type','National ID Issued Country','National ID Issued Date','National ID Expire Date','National ID (2)','National ID Type (2)','National ID Issued Country (2)','National ID Issued Date (2)',' National ID Expire Date (2)','Visa Country','Visa Type','Visa Number','Visa Issued Date','Visa Expire Date','Passenger Email Address','Tel Country Code','Phone Number','Emergency Contact Name','Emergency Contact Email','Emergency Tel Country Code','Emergency Contact No.','Paid','Amount','Form of Payment',' Cash Description/ Check Number','Received From','Comments','SourceID','Address','City','Country Code','State/Province','District','Zip Code','AUTO BC','AUTO CS','SHOREX','ADDON','CBS RETAIL PROGRAM','AMOUNT CBS','ClubAccNumber','TierLevel','GroupID','ReserveType','Alt Res ID','Remark'];                           
        $titleArray2 = ['','','','','','','','团号','','','','','航次（出发日期）','房间类别','房间序號','','性别','性别','名字（英文/拼音）','姓氏（英文/拼音）', '名字（中文）', '姓氏（中文）', '出生日期','国 籍','','','护照号码','','','护照到期日','往來港澳通行证号码（中国居民） / 身份证号码（香港、澳门居民）', '证件类型','','','证件有效日期','证件号码（选填）', '证件类型（选填）','','','证件有效日期（选填）','','','','','','','','','','','','','','','','','','','','','','','','','','','','岸上观光团' ,'落地签 | 穿梭巴士编码（前往登船）| 穿梭巴士编码（回程离开）| 提前支付邮轮服务费 ','','','','','团号','','','备注'];
        $sheet->fromArray($titleArray1, NULL, 'A1');
        $sheet->fromArray($titleArray2, NULL, 'A2');

        $sheet->getStyle("A1:BY2")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_BOTTOM);

        $sheet->getStyle('A1:P2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        $sheet->getStyle('Q1:AZ2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('953735');
        $sheet->getStyle('BA1:BG2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('007E39');
        $sheet->getStyle('BH1:BO2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('595959');
        $sheet->getStyle('BP1:BY2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('31859C');
        
        $sheet->getStyle('A1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
        $sheet->getStyle('B1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('C1:F1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
        $sheet->getStyle('G1')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('H1:I2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
        $sheet->getStyle('J1:L2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('M1:AI2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
        $sheet->getStyle('AJ1:AS2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');
        $sheet->getStyle('AT1:AZ2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFF00');
        $sheet->getStyle('BA1:BY2')->getFont()->setSize(10)->setBold(true)->getColor()->setARGB('FFFFFF');

        $sheet->getStyle('A2:BY2')->getFont()->setSize(10)->setBold(true)->setName('宋体');
        $sheet->getStyle('A1:BY2')->getAlignment()->setWrapText(true);

        $sheet->getColumnDimension('H')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('M')->setWidth(18);// 设置宽度
        $sheet->getColumnDimension('N')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('O')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('S')->setWidth(18);// 设置宽度
        $sheet->getColumnDimension('T')->setWidth(18);// 设置宽度
        $sheet->getColumnDimension('U')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('V')->setWidth(15);// 设置宽度 
        $sheet->getColumnDimension('W')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('AD')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('AE')->setWidth(30);// 设置宽度
        $sheet->getColumnDimension('AN')->setWidth(18);// 设置宽度
        $sheet->getColumnDimension('BP')->setWidth(15);// 设置宽度
        $sheet->getColumnDimension('BQ')->setWidth(40);// 设置宽度
     
        $room_models = RoomUse::find()->where(['in','id',$room_arr])->all();

        $m_gender = [
            1 => 'Mr',
            2 => 'Ms',
        ];
        //省份中英文
        $province = yii::$app->params['area']['province'];
        $province = array_flip($province);
        $province = array_merge($province,yii::$app->params['area']['country_english']);
        $country_code_2 = yii::$app->params['area']['country_code_2'];

        $key = 1;

         //先找房间 再找人
        foreach($room_models as $room){
          
            //房间名称
            $room_name = $room->room->name.$room->room->code." ".$room->num.'人间';
            //房间类型
            $room_type = $room->room->code;
            //房间号
            $room_code = $room->code;
            //最大入住人数
            $room_num = $room->room->num;
            $room_remark = $room->remark;
            foreach($room->allCustomer as $val){   
                
                $sheet->setCellValue(sprintf("H%d",$key+2),$val->group);
                $sheet->setCellValue(sprintf("N%d",$key+2),$room_type);
                $sheet->setCellValueExplicit(sprintf("O%d",$key+2),$room->code,\PHPExcel_Cell_DataType::TYPE_STRING);
                $sheet->setCellValue(sprintf("Q%d",$key+2),$m_gender[$val->gender]);
                $sheet->setCellValue(sprintf("R%d",$key+2),yii::$app->params['gender_en'][$val->gender]);
                $sheet->setCellValue(sprintf("S%d",$key+2),$val->en_last_name);
                $sheet->setCellValue(sprintf("T%d",$key+2),$val->en_first_name);
                $sheet->setCellValue(sprintf("U%d",$key+2),$val->last_name);
                $sheet->setCellValue(sprintf("V%d",$key+2),$val->first_name);

                $sheet->setCellValue(sprintf("W%d",$key+2),!empty($val->birthday) ? date("d/m/Y",strtotime($val->birthday)) :'');
                $sheet->setCellValue(sprintf("X%d",$key+2), (!empty($country_code_2[$val->country]))?$country_code_2[$val->country]:$val->country);
                $sheet->setCellValue(sprintf("AA%d",$key+2),$val->passport);
                $sheet->setCellValue(sprintf("AD%d",$key+2), !empty($val->passport_date) ? date("d/m/Y",strtotime($val->passport_date)) :'');

                $sheet->setCellValue(sprintf("BY%d",$key+2),$room_remark);
                $key++;
            }       

        }   

        
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $fileName = sprintf("%s.xlsx",uniqid());
        $path = 'uploads/roomexcel/'.date('Ym').'/';
        if(!is_dir($path)){  
              mkdir($path,0777,true);  
        }
        $file = $path.$fileName;
        $objWriter->save($file);

        $models = RoomUse::find()->where(['in','id',$room_arr])->all();
        foreach($models as $model) {
            $model->download = 2;
            $model->update(false); // skipping validation as no user input is involved
        }
       
        //获取现有分团的最大值
        $i = RoomDownload::find()->where(['cruise_id'=>$cruise_id])->count();
        
        $down_name = sprintf("%s_%s_第%s次",$cruise->name,yii::$app->params['template_2'][9],$i+1);
        $down_name = str_replace("\"","",$down_name); //删除双引号
        $down_name = str_replace("'","",$down_name);  //删除单引号
        $down_name = preg_replace("/\s/","",$down_name); //去除空格

        $ord = new RoomDownload();
        $ord->cruise_id = $cruise_id;
        $ord->name = $down_name;
        $ord->path = $file;
        $ord->download_id = Yii::$app->user->id;
        $ord->num = $i+1;
        $ord->down_time = date("Y-m-d H:i:s");
        $ord->status = 1;
        $ord->template_id = 9;
        $ord->save();

        $res = [ 
                'down_name' => $down_name.".xlsx",
                'down_url'  => $file,
              ];  
        return $res;
    }


}
